﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.Entity.Financial_Management;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.IRepository.Financial_Management;
using Domain.IRepository.System_operation;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Financial_Management
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Receivable_meterRepository:IReceivable_meterRepository
    {
        public MyDbContext myDbContext { get; set; }

        public Receivable_meterRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public int Count(string? Sign_time, string? value2, string? Receipt_type, string? Ord_state)
        {
            string sql = @"select A.Receivable_number,B.Ord_number,C.[Ct_numbering],C.[Ct_cl_name],B.Ord_state,D.Sign_time,B.Shipments_addres,B.Receiving_addres,
A.Billing_status,A.Billing_time,A.Statement_generation,A.Statement_number,B.Freight_count,B.Insurance,B.Packag_fee,B.Handling_fee,
B.Transit_fees,B.Fuel_costs,B.Service_charge,B.Other_fee,B.Pay_as_you_go,B.Prepayment,B.Collect,B.Periodical_payment,A.[Reconciled_amounts],
A.[UnReconciled_amounts],A.[Invoicing_status],A.[Invoicing_amounts],A.[UnInvoicing_amounts],A.[Redemption_status],
A.[Redemption_amounts],A.[UnRedemption_amounts],A.[Chargebacks_amounts],B.Shipping_time,B.Ord_startaddress,B.Ord_destination,
B.Carrier_name,B.Driver_name,B.Driver_phone,B.Create_time,B.Create_by from[dbo].[Receivable_meter] A join Orders B on A.Ord_id = B.Ord_id
join[dbo].[Customer_ment] C ON A.Ct_id = C.Ct_id
join[dbo].[Signature_receipt] D ON A.Sign_id = D.Sign_id";
            if (Sign_time != null && Sign_time != "")
            {
                string[] time1 = Sign_time.Split(',');
                sql += $" and [Sign_time]>='{time1[0]}' and [Sign_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Customer LIKE '%{value2}%')";
            }
            if (Receipt_type != null && Receipt_type != "")
            {
                sql += $" and [Receipt_type] like '%{Receipt_type}%'";
            }
            if (Ord_state != null && Ord_state != "")
            {
                sql += $" and [Ord_state] like '%{Ord_state}%'";
            }
            int count = myDbContext.Database.GetDbConnection().Query<Receivable_meter>(sql).Count();
            return count;
        }

        public List<Receivable_meter> GetReceivable_meter()
        {
            string sql = "SELECT * FROM [dbo].[Receivable_meter]";
            List<Receivable_meter> zhi =  myDbContext.Database.GetDbConnection().Query<Receivable_meter>(sql).ToList();
            return zhi;
        }

        public Orders GetOrd_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Orders] where [Ord_id]={id}";
            Orders zhi= myDbContext.Database.GetDbConnection().Query<Orders>(sql).FirstOrDefault();
            return zhi;
        }

        public Signature_receipt GetSign_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Signature_receipt] where [Sign_id]={id}";
            return myDbContext.Database.GetDbConnection().Query<Signature_receipt>(sql).FirstOrDefault();
        }

        public Customer_ment GetCustomer_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Customer_ment] where [Ct_id]={id}";
            return myDbContext.Database.GetDbConnection().Query<Customer_ment>(sql).FirstOrDefault();
        }

        public List<Receivable_meter> GetReceivable_meter_Update(int Receivable_Id)
        {
            string sql = $@"select * from [dbo].[Receivable_meter] where Receivable_Id='{Receivable_Id}'";
            return myDbContext.Database.GetDbConnection().Query<Receivable_meter>(sql).ToList();
        }

        public int Update(Receivable_meter receivable_meter)
        {
            string sql = $@"UPDATE [dbo].[Receivable_meter] SET [Paid_up_Pay] = '{receivable_meter.Paid_up_Pay}',[Paid_up_prepayment] ='{receivable_meter.Paid_up_prepayment}',[Paid_up_collect] = '{receivable_meter.Paid_up_collect}',[Paid_up_periodical] = '{receivable_meter.Paid_up_periodical}' WHERE Receivable_Id='{receivable_meter.Receivable_Id}'";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }
    }
}
